package edu.cdbi.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import edu.cdbi.model.instance.Course;

public class CourseManagement {

	public CourseManagement() {
		
	}
	
	public Integer insert(String teacherId, String name){
		Connection connection = DatabaseManager.getInstance().createConnection();
		if(connection == null){
			return null;
		}
		
		try {
			PreparedStatement statement = connection.prepareStatement(
					"INSERT INTO course (teacher_id, name) VALUES(?, ?)");
			statement.setString(1, teacherId);
			statement.setString(2, name);
			statement.executeUpdate();
			
			statement = connection.prepareStatement("SELECT @@identity");
			ResultSet resultSet = statement.executeQuery();
			resultSet.next();
			int number = resultSet.getInt(1);
			statement.close();
			statement.close();
			connection.close();
			return number;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public List<Course> getList(){
		Connection connection = DatabaseManager.getInstance().createConnection();
		if(connection == null){
			return null;
		}
		try {
			PreparedStatement statement = connection.prepareStatement(
					"SELECT course_id, teacher_id, name FROM course");
			ResultSet resultSet = statement.executeQuery();
			List<Course> list = new ArrayList<Course>();
			while(resultSet.next()){
				Course course = new Course();
				course.setId(resultSet.getInt("course_id"));
				course.setTeacherId(resultSet.getInt("teacher_id"));
				course.setName(resultSet.getString("name"));
				list.add(course);
			}
			resultSet.close();
			statement.close();
			connection.close();
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public Course getData(int id){
		Connection connection = DatabaseManager.getInstance().createConnection();
		if(connection == null){
			return null;
		}
		try {
			PreparedStatement statement = connection.prepareStatement(
					"SELECT course_id, teacher_id, name FROM course WHERE course_id=?");
			statement.setInt(1, id);
			ResultSet resultSet = statement.executeQuery();
			Course course = null;
			if(resultSet.next()){
				course = new Course();
				course.setId(resultSet.getInt("course_id"));
				course.setTeacherId(resultSet.getInt("teacher_id"));
				course.setName(resultSet.getString("name"));
			}
			resultSet.close();
			statement.close();
			connection.close();
			return course;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public boolean delete(int id) {
		Connection connection = DatabaseManager.getInstance()
				.createConnection();
		if (connection == null) {
			return false;
		}
		try {
			PreparedStatement statement = connection
					.prepareStatement("DELETE FROM course WHERE course_id=" + id);
			statement.executeUpdate();
			statement.close();
			connection.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
}
